﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Web;
using System.Configuration;
using System.Data;
using System.Data.SqlClient;

using permit.App_Code.Entity;

namespace permit.App_Code.DataAccess
{
    public class UserAccess
    {

        string strConn = ConfigurationManager.ConnectionStrings["permitConnectionString"].ConnectionString;
        SqlConnection conn;

        public UserAccess()
        {

        }
        public SqlConnection GetConnetcion(string connString)
        {
            conn = new SqlConnection(connString);
            if (conn.State == ConnectionState.Closed)
            {

                conn.Open();
            }
            return conn;
        }

        #region
        /// <summary>
        /// 从数据库中查询数据的,返回单条记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public UserTable GetUser(string userName, string userPassword)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT UserTable.user_ID, UserTable.user_Name, UserTable.user_Password,      UserTable.user_Position, UserTable.dep_ID, UserTable.Name,DepartmentTable.dep_Print, DepartmentTable.dep_AreaCode FROM UserTable INNER JOIN   DepartmentTable ON UserTable.dep_ID = DepartmentTable.dep_ID where user_Name = @userName and user_Password = @password";

            command.Parameters.Add("@userName", SqlDbType.VarChar, 50, "user_Name");
            command.Parameters.Add("@password", SqlDbType.VarChar, 50, "user_password");

            command.Parameters["@userName"].Value = userName;
            command.Parameters["@password"].Value = userPassword;
            command.Connection = GetConnetcion(strConn);


            SqlDataReader reader = command.ExecuteReader();
            int count = 0;
            UserTable userData = new UserTable();

            while (reader.Read())
            {

                userData.USER_ID = Convert.ToInt32(reader["user_ID"]);
                userData.USER_POSITION = reader["user_Position"].ToString();
                userData.DEP_ID = Convert.ToInt32(reader["dep_ID"]);
                userData.NAME = userName;
                userData.USER_PASSWORD = userPassword;
                userData.DEP_PRINT = Convert.ToInt32(reader["dep_Print"]);
                userData.DEP_AREACODE = reader["dep_AreaCode"].ToString();

                count++;
            }

            reader.Dispose();
            connClose();//关闭连接
            if (count > 0)
                return userData;//返回结果
            else
                return null;//没有查询到数据时返回null

        }

        public bool GetUser(string userName)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "select * from UserTable where user_Name = @userName";
            command.Parameters.Add("@userName", SqlDbType.VarChar, 50, "user_Name");
            command.Parameters["@userName"].Value = userName;
            command.Connection = GetConnetcion(strConn);


            SqlDataReader reader = command.ExecuteReader();
            int count = 0;

            while (reader.Read())
            {
                count++;
                break;
            }


            connClose();//关闭连接
            if (count > 0)
                return true;//返回结果
            else
                return false;//没有查询到数据时返回null

        }

        #endregion

        /// <summary>
        /// 从数据库中查询数据的,返回记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetUsers(int depID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT DepartmentTable.dep_Name, UserTable.user_Password, UserTable.Name,UserTable.user_Name, UserTable.user_ID, UserTable.user_Position, UserTable.dep_ID FROM DepartmentTable INNER JOIN UserTable ON DepartmentTable.dep_ID = UserTable.dep_ID WHERE (UserTable.dep_ID = @dep_ID)";

            command.Parameters.Add("@dep_ID", SqlDbType.Int, 4, "dep_ID").Value = depID;

            command.Connection = GetConnetcion(strConn);

            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                da.Dispose();
                return ds;//返回结果
            }
            catch (Exception ex)
            {
                return null;
            }


        }

        /// <summary>
        /// 从数据库中查询所有数据的,返回记录
        /// </summary>
        /// <param name="sql"></param>
        /// <returns></returns>
        public DataSet GetUsers()
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "SELECT Name, user_Name, user_ID, dep_ID FROM UserTable";
            command.Connection = GetConnetcion(strConn);

            SqlDataAdapter da = new SqlDataAdapter(command);
            DataSet ds = new DataSet();
            try
            {
                da.Fill(ds);
                da.Dispose();
                return ds;//返回结果
            }
            catch (Exception ex)
            {
                return null;
            }


        }


        /// <summary>
        /// 从数据库中查询数据的,返回单条记录
        /// </summary>
        /// <param name="userID"></param>
        /// <returns></returns>
        public UserTable getUserByID(int userID)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "SELECT user_ID, user_Name,user_Password, Name, user_Password, user_Position, dep_ID FROM UserTable WHERE (user_ID = @user_ID)";

            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;

            command.Connection = GetConnetcion(strConn);

            SqlDataReader reader = command.ExecuteReader();
            int count = 0;
            UserTable userData = new UserTable();

            while (reader.Read())
            {

                userData.USER_ID = Convert.ToInt32(reader["user_ID"]);
                userData.USER_POSITION = reader["user_Position"].ToString();
                userData.DEP_ID = Convert.ToInt32(reader["dep_ID"]);
                userData.NAME = reader["Name"].ToString();
                userData.USER_PASSWORD = reader["user_Password"].ToString();
                userData.USER_NAME = reader["user_Name"].ToString();

                count++;
            }

            reader.Dispose();
            connClose();//关闭连接
            if (count > 0)
                return userData;//返回结果
            else
                return null;//没有查询到数据时返回null
        }

        public void connClose()
        {
            if (conn.State == ConnectionState.Open)
            {//判断数据库的连接状态，如果状态是打开的话就将它关闭

                conn.Close();
            }
        }


        public int UpdateUser(UserTable user)
        {
            SqlCommand command = new SqlCommand();
            command.CommandText = "UPDATE UserTable SET user_Name = @user_Name, user_Position = @user_Position,user_Password = @user_Password,dep_ID = @dep_ID, Name = @Name WHERE (user_ID = @user_ID)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@user_Name", SqlDbType.VarChar, 50, "user_Name").Value = user.USER_NAME;
            command.Parameters.Add("@user_Position", SqlDbType.VarChar, 50, "user_Position").Value = user.USER_POSITION;
            command.Parameters.Add("@user_Password", SqlDbType.VarChar, 50, "user_Password").Value = user.USER_PASSWORD;
            command.Parameters.Add("@dep_ID", SqlDbType.Int, 4, "dep_ID").Value = user.DEP_ID;
            command.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name").Value = user.NAME;

            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = user.USER_ID;

            int count;
            try
            {
                count = command.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                return 0;
            }


            connClose();//关闭连接

            return count;//返回结果


        }

        /// <summary>
        /// 插入数据，影响返回行数
        /// </summary>
        /// <param name="permit"></param>
        /// <returns></returns>
        public int InsertUser(UserTable user)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "INSERT INTO UserTable(user_Name,user_Position,user_Password ,dep_ID , Name)VALUES (@user_Name,@user_Position,@user_Password,@dep_ID,@Name)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@user_Name", SqlDbType.VarChar, 50, "user_Name").Value = user.USER_NAME;
            command.Parameters.Add("@user_Position", SqlDbType.VarChar, 50, "user_Position").Value = user.USER_POSITION;
            command.Parameters.Add("@user_Password", SqlDbType.VarChar, 50, "user_Password").Value = user.USER_PASSWORD;
            command.Parameters.Add("@dep_ID", SqlDbType.Int, 4, "dep_ID").Value = user.DEP_ID;
            command.Parameters.Add("@Name", SqlDbType.VarChar, 50, "Name").Value = user.NAME;

            try
            {
                int count = command.ExecuteNonQuery();
                connClose();//关闭连接
                return count;
            }
            catch (Exception ex)
            {
                connClose();//关闭连接
                return 0;
            }


        }

        /// <summary>
        /// 删除数据，影响返回行数
        /// </summary>
        /// <param name="permit"></param>
        /// <returns></returns>
        public int DeleteUser(int userID)
        {
            SqlCommand command = new SqlCommand();

            command.CommandText = "DELETE FROM UserTable WHERE (user_ID = @user_ID)";

            command.Connection = GetConnetcion(strConn);

            command.Parameters.Add("@user_ID", SqlDbType.Int, 4, "user_ID").Value = userID;


            try
            {
                int count = command.ExecuteNonQuery();
                connClose();//关闭连接
                return count;
            }
            catch (Exception ex)
            {
                connClose();//关闭连接
                return 0;
            }


        }
    }
}


